﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Common;
using System.Data;

namespace WpfListingClientSociete
{
    class SingletonClients
    {
        public static Clients ListeClients = new Clients();
    }
    class Clients
    {
        public Clients()
        {
        }

        public Client[] Tableau = new Client[0];


        public void AjouterClientTableau(Client C)
        {
            Client[] Tableau2 = new Client[Tableau.Length + 1];
            Tableau.CopyTo(Tableau2, 0);
            Tableau2[Tableau2.Length - 1] = C;
            Tableau = Tableau2;

             if (ListUpdated != null)
            {
                ListUpdated(this, new UpdateEventArgs("AjouterClientTableau"));
            }
            //SauverClient();      
        }

        public void AjouterClientBDD(Client C)
        {
           
            DbProviderFactory dbpf;
            DbConnection oConn;
            DbDataAdapter oDa;
            dbpf = DbProviderFactories.GetFactory("System.Data.OLEDB");
            oConn = dbpf.CreateConnection();
            oConn.ConnectionString = @"Provider = MICROSOFT.ACE.OLEDB.12.0;Data Source = C:\Users\Ludovic\Documents\3ème SI\Informatique\BDDListingClientSociété.accdb ;";
            oDa = dbpf.CreateDataAdapter();
            oDa.SelectCommand = oConn.CreateCommand();
            oDa.SelectCommand.CommandText = "SELECT * FROM CLIENTS;";
            DataSet oDS = new DataSet();
            oDa.Fill(oDS, "Clients");

            Tableau = new Client[0];

            DataRow dr = oDS.Tables[0].NewRow();

            dr[1] = C.Nom;
            dr[2] = C.Prenom;
            dr[3] = C.Ville;
            dr[4] = C.Rue;
            dr[5] = System.Convert.ToInt32(C.Num);
            dr[6] = System.Convert.ToInt32(C.CP);
            dr[7] = C.Pays;
            dr[8] = C.Fixe;
            dr[9] = C.Mobile;
            dr[10] = C.Mail;
            dr[11] = System.Convert.ToBoolean(C.Genre);
            dr[12] = System.Convert.ToDateTime(C.DateNaissance);
            dr[13] = C.NTVA;
            
            oDS.Tables[0].Rows.Add(dr);
            oDa.InsertCommand = oConn.CreateCommand();
            oDa.InsertCommand.CommandText = "INSERT INTO CLIENTS (Nom,Prenom,Ville,Rue,Num,CP,Pays,Fixe,Mobile,Mail,Genre,DateNaissance,NTVA) VALUES (@NOM,@Prenom,@Ville,@Rue,@Num,@CP,@Pays,@Fixe,@Mobile,@Mail,@Genre,@DateNaissance,@NTVA);";
            
            DbParameter Para1 = oDa.InsertCommand.CreateParameter();
            Para1.SourceColumn = "Nom";
            Para1.ParameterName = "Nom";
            oDa.InsertCommand.Parameters.Add(Para1);

            DbParameter Para2 = oDa.InsertCommand.CreateParameter();
            Para2.SourceColumn = "Prenom";
            Para2.ParameterName = "Prenom";
            oDa.InsertCommand.Parameters.Add(Para2);

            DbParameter Para3 = oDa.InsertCommand.CreateParameter();
            Para3.SourceColumn = "Ville";
            Para3.ParameterName = "Ville";
            oDa.InsertCommand.Parameters.Add(Para3);

            DbParameter Para4 = oDa.InsertCommand.CreateParameter();
            Para4.SourceColumn = "Rue";
            Para4.ParameterName = "Rue";
            oDa.InsertCommand.Parameters.Add(Para4);

            DbParameter Para5 = oDa.InsertCommand.CreateParameter();
            Para5.SourceColumn = "Num";
            Para5.ParameterName = "Num";
            oDa.InsertCommand.Parameters.Add(Para5);

            DbParameter Para6 = oDa.InsertCommand.CreateParameter();
            Para6.SourceColumn = "CP";
            Para6.ParameterName = "CP";
            oDa.InsertCommand.Parameters.Add(Para6);

            DbParameter Para7 = oDa.InsertCommand.CreateParameter();
            Para7.SourceColumn = "Pays";
            Para7.ParameterName = "Pays";
            oDa.InsertCommand.Parameters.Add(Para7);

            DbParameter Para8 = oDa.InsertCommand.CreateParameter();
            Para8.SourceColumn = "Fixe";
            Para8.ParameterName = "Fixe";
            oDa.InsertCommand.Parameters.Add(Para8);

            DbParameter Para9 = oDa.InsertCommand.CreateParameter();
            Para9.SourceColumn = "Mobile";
            Para9.ParameterName = "Mobile";
            oDa.InsertCommand.Parameters.Add(Para9);

            DbParameter Para10 = oDa.InsertCommand.CreateParameter();
            Para10.SourceColumn = "Mail";
            Para10.ParameterName = "Mail";
            oDa.InsertCommand.Parameters.Add(Para10);

            DbParameter Para11 = oDa.InsertCommand.CreateParameter();
            Para11.SourceColumn = "Genre";
            Para11.ParameterName = "Genre";
            oDa.InsertCommand.Parameters.Add(Para11);

            DbParameter Para12 = oDa.InsertCommand.CreateParameter();
            Para12.SourceColumn = "DateNaissance";
            Para12.ParameterName = "DateNaissance";
            oDa.InsertCommand.Parameters.Add(Para12);

            DbParameter Para13 = oDa.InsertCommand.CreateParameter();
            Para13.SourceColumn = "NTVA";
            Para13.ParameterName = "NTVA";
            oDa.InsertCommand.Parameters.Add(Para13);

            oDa.Update(oDS.Tables[0]);

            ChargerClient();

            if (ListUpdated != null)
            {
                ListUpdated(this, new UpdateEventArgs("AjouterClientBBD"));
            }
            
        }

        public void ModifierClientBDD( int aNumClient, Client C)
        {
            DbProviderFactory dbpf;
            DbConnection oConn;
            DbDataAdapter oDa;
            dbpf = DbProviderFactories.GetFactory("System.Data.OLEDB");
            oConn = dbpf.CreateConnection();
            oConn.ConnectionString = @"Provider = MICROSOFT.ACE.OLEDB.12.0;Data Source = C:\Users\Ludovic\Documents\3ème SI\Informatique\BDDListingClientSociété.accdb ;";
            oDa = dbpf.CreateDataAdapter();
            oDa.SelectCommand = oConn.CreateCommand();
            oDa.SelectCommand.CommandText = "SELECT * FROM CLIENTS";
            DataSet oDS = new DataSet();
            oDa.Fill(oDS, "Clients");

            Tableau = new Client[0];

            DataRow dr = oDS.Tables[0].Rows[0];

            dr[1] = C.Nom;
            dr[2] = C.Prenom;
            dr[3] = C.Ville;
            dr[4] = C.Rue;
            dr[5] = C.Num;
            dr[6] = C.CP;
            dr[7] = C.Pays;
            dr[8] = C.Fixe;
            dr[9] = C.Mobile;
            dr[10] = C.Mail;
            dr[11] = C.Genre;
            dr[12] = C.DateNaissance;
            dr[13] = C.NTVA;

            oDa.UpdateCommand = oConn.CreateCommand();
            oDa.UpdateCommand.CommandText = "UPDATE Clients SET Nom = @NOM, Prenom = @Prenom, Ville = @Ville, Rue = @Rue, Num = @Num, CP = @CP,Pays = @Pays, Fixe = @Fixe, Mobile = @Mobile, Mail = @Mail, Genre = @Genre, DateNaissance = @DateNaissance, NTVA = @NTVA WHERE NumeroClient = " + aNumClient.ToString() + ";";

            DbParameter Para1 = oDa.UpdateCommand.CreateParameter();
            Para1.SourceColumn = "Nom";
            Para1.ParameterName = "Nom";
            oDa.UpdateCommand.Parameters.Add(Para1);

            DbParameter Para2 = oDa.UpdateCommand.CreateParameter();
            Para2.SourceColumn = "Prenom";
            Para2.ParameterName = "Prenom";
            oDa.UpdateCommand.Parameters.Add(Para2);

            DbParameter Para3 = oDa.UpdateCommand.CreateParameter();
            Para3.SourceColumn = "Ville";
            Para3.ParameterName = "Ville";
            oDa.UpdateCommand.Parameters.Add(Para3);

            DbParameter Para4 = oDa.UpdateCommand.CreateParameter();
            Para4.SourceColumn = "Rue";
            Para4.ParameterName = "Rue";
            oDa.UpdateCommand.Parameters.Add(Para4);

            DbParameter Para5 = oDa.UpdateCommand.CreateParameter();
            Para5.SourceColumn = "Num";
            Para5.ParameterName = "Num";
            oDa.UpdateCommand.Parameters.Add(Para5);

            DbParameter Para6 = oDa.UpdateCommand.CreateParameter();
            Para6.SourceColumn = "CP";
            Para6.ParameterName = "CP";
            oDa.UpdateCommand.Parameters.Add(Para6);

            DbParameter Para7 = oDa.UpdateCommand.CreateParameter();
            Para7.SourceColumn = "Pays";
            Para7.ParameterName = "Pays";
            oDa.UpdateCommand.Parameters.Add(Para7);

            DbParameter Para8 = oDa.UpdateCommand.CreateParameter();
            Para8.SourceColumn = "Fixe";
            Para8.ParameterName = "Fixe";
            oDa.UpdateCommand.Parameters.Add(Para8);

            DbParameter Para9 = oDa.UpdateCommand.CreateParameter();
            Para9.SourceColumn = "Mobile";
            Para9.ParameterName = "Mobile";
            oDa.UpdateCommand.Parameters.Add(Para9);

            DbParameter Para10 = oDa.UpdateCommand.CreateParameter();
            Para10.SourceColumn = "Mail";
            Para10.ParameterName = "Mail";
            oDa.UpdateCommand.Parameters.Add(Para10);

            DbParameter Para11 = oDa.UpdateCommand.CreateParameter();
            Para11.SourceColumn = "Genre";
            Para11.ParameterName = "Genre";
            oDa.UpdateCommand.Parameters.Add(Para11);

            DbParameter Para12 = oDa.UpdateCommand.CreateParameter();
            Para12.SourceColumn = "DateNaissance";
            Para12.ParameterName = "DateNaissance";
            oDa.UpdateCommand.Parameters.Add(Para12);

            DbParameter Para13 = oDa.UpdateCommand.CreateParameter();
            Para13.SourceColumn = "NTVA";
            Para13.ParameterName = "NTVA";
            oDa.UpdateCommand.Parameters.Add(Para13);


            oDa.Update(oDS.Tables[0]);

            ChargerClient();

            if (ListUpdated != null)
            {
                ListUpdated(this, new UpdateEventArgs("ModifierClientBBD"));
            }
        }


        public void ChargerClient()
        {
            DbProviderFactory dbpf;
            DbConnection oConn;
            DbDataAdapter oDa;
            dbpf = DbProviderFactories.GetFactory("System.Data.OLEDB");
            oConn = dbpf.CreateConnection();
            oConn.ConnectionString = @"Provider = MICROSOFT.ACE.OLEDB.12.0;Data Source = C:\Users\Ludovic\Documents\3ème SI\Informatique\BDDListingClientSociété.accdb ;";
            oDa = dbpf.CreateDataAdapter();
            oDa.SelectCommand = oConn.CreateCommand();
            oDa.SelectCommand.CommandText = "SELECT * FROM CLIENTS;";
            DataSet oDS = new DataSet();
            oDa.Fill(oDS, "Clients");

            Tableau = new Client[0];

            foreach (DataRow r in oDS.Tables[0].Rows)
            {
                Client C = new Client();
                C.NumClient = System.Convert.ToInt32(r[0]);
                C.Nom = r[1].ToString();
                C.Prenom = r[2].ToString();
                C.Ville = r[3].ToString();
                C.Rue = r[4].ToString();
                C.Num = r[5].ToString();
                C.CP = r[6].ToString();
                C.Pays = r[7].ToString();
                C.Fixe = r[8].ToString();
                C.Mobile = r[9].ToString();
                C.Mail = r[10].ToString();
                C.Genre = r[11].ToString();
                C.DateNaissance = r[12].ToString();
                C.NTVA = r[13].ToString();

                SingletonClients.ListeClients.AjouterClientTableau(C);

 
                if (ListUpdated != null)
                {
                    ListUpdated(this, new UpdateEventArgs("ChargerClient"));
                }

            }
        }

        public void SupprimerClient( int aNumClient)
        {
            DbProviderFactory dbpf;
            DbConnection oConn;
            DbDataAdapter oDa;

            dbpf = DbProviderFactories.GetFactory("System.Data.OLEDB");
            oConn = dbpf.CreateConnection();
            oConn.ConnectionString = @"Provider = MICROSOFT.ACE.OLEDB.12.0;Data Source = C:\Users\Ludovic\Documents\3ème SI\Informatique\BDDListingClientSociété.accdb ;";
            oDa = dbpf.CreateDataAdapter();
            oDa.SelectCommand = oConn.CreateCommand();
            oDa.SelectCommand.CommandText = "SELECT * FROM CLIENTS WHERE NumeroClient=" + aNumClient.ToString() + ";";
            DataSet oDS = new DataSet();
            oDa.Fill(oDS, "Clients");

            foreach (DataRow r in oDS.Tables[0].Rows)
            {
                r.Delete();
            }
            oDa.DeleteCommand = oConn.CreateCommand();
            oDa.DeleteCommand.CommandText = "DELETE FROM CLIENTS WHERE NumeroClient=? ;";
            DbParameter DBPara;
            DBPara = oDa.DeleteCommand.CreateParameter();
            DBPara.SourceColumn = "NumeroClient";
            oDa.DeleteCommand.Parameters.Add(DBPara);
            int n = oDa.Update(oDS.Tables[0]);

            ChargerClient();

            if (ListUpdated != null)
            {
                ListUpdated (this, new UpdateEventArgs("SupprimerClient"));
            }
        }

        public delegate void UpdateHandler(object sender, UpdateEventArgs e);
        public event UpdateHandler ListUpdated;

        public class UpdateEventArgs : EventArgs
        {
            private string cMethodeSource = "";
            public UpdateEventArgs(string aMethodeSource)
            {
                cMethodeSource = aMethodeSource;
            }
            public string MethodeSource
            {
                get { return cMethodeSource; }
            }
        }
     }
}
